#-*- codeing = utf-8 -*-
#@Time : 2020/11/26 15:21
#@Author : 阳某
#@File : 10.写数据.py
#@Software : PyCharm


import openpyxl

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()

# 创建时，会自动产生一个sheet，通过active获取
sh = book.active

# 修改当前 sheet 标题为 工资表
sh.title = '工资表'

# 保存文件
book.save('信息.xlsx')

# 增加一个名为 '年龄表' 的sheet，放在最后
sh1 = book.create_sheet('年龄表-最后')

# 增加一个 sheet，放在最前
sh2 = book.create_sheet('年龄表-最前',0)

# 增加一个 sheet，指定为第2个表单
sh3 = book.create_sheet('年龄表2',1)

# 根据名称获取某个sheet对象
sh = book['工资表']

# 给第一个单元格写入内容
sh['A1'] = '你好'

# 获取某个单元格内容
print(sh['A1'].value)

# 根据行号列号， 给第一个单元格写入内容，
# 注意和 xlrd 不同，是从 1 开始
sh.cell(2,2).value = '白月黑羽'

# 根据行号列号， 获取某个单元格内容
print(sh.cell(1, 1).value)

book.save('信息.xlsx')



import openpyxl

name2Age = {
    '张飞' :  38,
    '赵云' :  27,
    '许褚' :  36,
    '典韦' :  38,
    '关羽' :  39,
    '黄忠' :  49,
    '徐晃' :  43,
    '马超' :  23,
}

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()

# 创建时，会自动产生一个sheet，通过active获取
sh = book.active

sh.title = '年龄表'

# 写标题栏
sh['A1'] =  '姓名'
sh['B1'] =  '年龄'

# 写入内容
row = 2

for name,age in name2Age.items():
    sh.cell(row, 1).value = name
    sh.cell(row, 2).value = age
    row += 1

# 保存文件
book.save('信息1.xlsx')


# 如果你的数据在一个列表或者元组中，可以使用append方法在sheet的末尾添加新行，写入数据，比如
import openpyxl
name2Age = [
    ['张飞' ,  38 ] ,
    ['赵云' ,  27 ] ,
    ['许褚' ,  36 ] ,
    ['典韦' ,  38 ] ,
    ['关羽' ,  39 ] ,
    ['黄忠' ,  49 ] ,
    ['徐晃' ,  43 ] ,
    ['马超' ,  23 ]
]

# 创建一个Excel workbook 对象
book = openpyxl.Workbook()
sh = book.active
sh.title = '年龄表'

# 写标题栏
sh['A1'] =  '姓名'
sh['B1'] =  '年龄'

for row in name2Age:
    # 添加到下一行的数据
    sh.append(row)

# 保存文件
book.save('信息2.xlsx')


# 修改Excel中的数据
import openpyxl
# 加载 excel 文件
wb = openpyxl.load_workbook('income.xlsx')
# 得到sheet对象
sheet = wb['2017']
sheet['A1'] = '修改一下'
## 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')



import openpyxl
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 在第2行的位置插入1行
sheet.insert_rows(2)
# 在第3行的位置插入3行
sheet.insert_rows(3,3)
# 在第2列的位置插入1列
sheet.insert_cols(2)
# 在第2列的位置插入3列
sheet.insert_cols(2,3)
## 指定不同的文件名，可以另存为别的文件
wb.save('income-2.xlsx')


import openpyxl
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 在第2行的位置删除1行
sheet.delete_rows(2)
# 在第3行的位置删除3行
sheet.delete_rows(3,3)
# 在第2列的位置删除1列
sheet.delete_cols(2)
# 在第3列的位置删除3列
sheet.delete_cols(3,3)
## 指定不同的文件名，可以另存为别的文件
wb.save('income-3.xlsx')


import openpyxl
# 导入Font对象 和 colors 颜色常量
from openpyxl.styles import Font,colors

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 指定单元格字体颜色，
sheet['A1'].font = Font(color=colors.RED, #使用预置的颜色常量
                        size=15,    # 设定文字大小
                        bold=True,  # 设定为粗体
                        italic=True # 设定为斜体
                        )

# 也可以使用RGB数字表示的颜色
sheet['B1'].font = Font(color="981818")

# 指定整行 字体风格， 这里指定的是第3行
font = Font(color="981818")
for y in range(1, 100): # 第 1 到 100 列
    sheet.cell(row=3, column=y).font = font

# 指定整列 字体风格， 这里指定的是第2列
font = Font(bold=True)
for x in range(1, 100): # 第 1 到 100 行
    sheet.cell(row=x, column=2).font = font

wb.save('income-1.xlsx')



import openpyxl
# 导入Font对象 和 colors 颜色常量
from openpyxl.styles import PatternFill
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']
# 指定 某个单元格背景色
sheet['A1'].fill = PatternFill("solid", "E39191")
# 指定 整行 背景色， 这里指定的是第2行
fill = PatternFill("solid", "E39191")
for y in range(1, 100): # 第 1 到 100 列
    sheet.cell(row=2, column=y).fill = fill
wb.save('income-1.xlsx')



import openpyxl
from openpyxl.drawing.image import Image

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 在第1行，第4列 的位置插入图片
sheet.add_image(Image('qr.png'), 'D1')

## 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')


'''
Excel COM接口
在Windows平台上，还可以通过 Excel 应用的 COM 接口 来对Excel进行操作。

这个方法相当于使用Python程序 通过 Excel应用 自己去修改，当然没有任何的副作用

而且能够实现一些特殊的功能，比如 自动打印Excel、合并单元格 等。

COM接口的特点是：打开文件快，读写速度慢。
使用 Excel COM 接口 打开 超大 Excel文件 比上面的两个库 要快很多。因为Excel程序本身的优化，可以部分加载，而上面的两个库是全部先读入内存。
如果你只是从 大Excel文件中 读取或修改少量数据，Excel COM 接口会快很多。
但是，如果你要读取大Excel中的大量数据，不要使用 COM接口，会非常的慢。
使用 Excel COM 接口，首先需要安装pywin32库，在命令行窗口输入如下命令：
'''

import win32com.client
excel = win32com.client.Dispatch("Excel.Application")

# excel.Visible = True     # 可以让excel 可见

# 这里填写要修改的Excel文件的绝对路径
workbook = excel.Workbooks.Open(r"d:\tmp\income1.xlsx")

# 得到 2017 表单
sheet = workbook.Sheets('2017')

# 修改表单第一行第一列单元格内容
# com接口，单元格行号、列号从1开始
sheet.Cells(1,1).Value="你好"

# 保存内容
workbook.Save()

# 关闭该Excel文件
workbook.Close()

# excel进程退出
excel.Quit()

# 释放相关资源
sheet = None
book = None
excel.Quit()
excel = None


# COM 接口、xlrd库 打开数据文件 和 读取数据的性能对比，大家可以参考下面这段代码。
import time

def byCom():
    t1 = time.time()
    import win32com.client
    excel = win32com.client.Dispatch("Excel.Application")

    # excel.Visible = True     # 可以让excel 可见
    workbook = excel.Workbooks.Open(r"h:\tmp\ruijia\数据.xlsx")

    sheet = workbook.Sheets(2)

    print(sheet.Cells(2,15).Value)
    print(sheet.UsedRange.Rows.Count)  #多少行

    t2 = time.time()
    print(f'打开: 耗时{t2 - t1}秒')

    total = 0
    for row in range(2,sheet.UsedRange.Rows.Count+1):
        value = sheet.Cells(row,15).Value
        if type(value) not in [int,float]:
            continue
        total += value

    print(total)

    t3 = time.time()
    print(f'读取数据: 耗时{t3 - t2}秒')


def byXlrd():
    t1 = time.time()
    import xlrd

    # 加载 excel 文件
    srcBook = xlrd.open_workbook("数据.xlsx")
    sheet = srcBook.sheet_by_index(1)

    print(sheet.cell_value(rowx=1,colx=14))
    print(sheet.nrows) #多少行

    t2 = time.time()
    print(f'打开: 耗时{t2 - t1}秒')

    total = 0
    for row in range(1,sheet.nrows):
        value = sheet.cell_value(row, 14)
        if type(value) == str:
            continue
        total += value

    print(total)

    t3 = time.time()
    print(f'读取数据: 耗时{t3 - t2}秒')

byCom()
byXlrd()